--    Author    : ChenErHao
--    Name      : DM.T_MTH_BAD_LOAN_DOWN_AMOUNT.HQL
--    Functions : 风险-不良贷款向下迁徙量
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-22  ChenErHao       1.CREATE THE PROCEDURE
--


-- 2019-01-22 标注
-- 这块关联感觉有点问题，数据表进行关联时没卡时间条件

INSERT OVERWRITE TABLE DM.T_MTH_BAD_LOAN_DOWN_AMOUNT PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
SELECT 
  T1.ORG_ID, 
  T1.CUSTOMER_TYPE,
  T1.PRODUCT_CATEGORY,
  T1.LOAN_PERIOD,
  T1.LPIC,
  T1.LOANS_ACTUALLY,
  T1.ENTERPRISE_SCALE,
  T1.ENTERPRISE_INVESTOR,
  T1.GUARANTY_STYLE,
  T1.LOAN_STATUS,
  T1.LOAN_QUALITY,
  T1.AREA_NO_ID,
  nvl(SUM(T1.LOAN_BALANCE),0) LOAN_BALANCE,
  nvl(SUM(T2.LOAN_BALANCE),0) LOAN_BALANCE_LM,
  nvl(SUM(T3.LOAN_BALANCE),0) LOAN_BALANCE_LY,
  nvl(SUM(T1.LOAN_BALANCE_RATE),0) LOAN_BALANCE_RATE
        FROM  DM.MID_MTH_BAD_LOAN_DOWN_AMOUNT_ONE T1
  LEFT JOIN DM.MID_MTH_BAD_LOAN_DOWN_AMOUNT_TWO T2  
    ON (T1.ORG_ID=T2.ORG_ID AND T1.CUSTOMER_TYPE=T2.CUSTOMER_TYPE 
   AND T1.PRODUCT_CATEGORY=T2.PRODUCT_CATEGORY AND T1.LOAN_PERIOD=T2.LOAN_PERIOD AND T1.LPIC=T2.LPIC
   AND T1.ENTERPRISE_SCALE=T2.ENTERPRISE_SCALE AND T1.LOANS_ACTUALLY=T2.LOANS_ACTUALLY 
   AND T1.ENTERPRISE_INVESTOR=T2.ENTERPRISE_INVESTOR AND T1.GUARANTY_STYLE=T2.GUARANTY_STYLE 
   AND T1.LOAN_STATUS=T2.LOAN_STATUS AND T1.LOAN_QUALITY=T2.LOAN_QUALITY AND T1.AREA_NO_ID=T2.AREA_NO_ID)
  LEFT JOIN DM.MID_MTH_BAD_LOAN_DOWN_AMOUNT_THREE T3  
    ON (T1.ORG_ID=T3.ORG_ID AND T1.CUSTOMER_TYPE=T3.CUSTOMER_TYPE 
   AND T1.PRODUCT_CATEGORY=T3.PRODUCT_CATEGORY AND T1.LOAN_PERIOD=T3.LOAN_PERIOD AND T1.LPIC=T3.LPIC
   AND T1.ENTERPRISE_SCALE=T3.ENTERPRISE_SCALE AND T1.LOANS_ACTUALLY=T3.LOANS_ACTUALLY 
   AND T1.ENTERPRISE_INVESTOR=T3.ENTERPRISE_INVESTOR AND T1.GUARANTY_STYLE=T3.GUARANTY_STYLE 
   AND T1.LOAN_STATUS=T3.LOAN_STATUS AND T1.LOAN_QUALITY=T3.LOAN_QUALITY AND T1.AREA_NO_ID=T3.AREA_NO_ID)
   WHERE T1.FISCAL_MTH_DIMNSN_ID='#V_DATA_MONTH#'
   GROUP BY 
  T1.ORG_ID,
  T1.CUSTOMER_TYPE,
  T1.PRODUCT_CATEGORY,
  T1.LOAN_PERIOD,
  T1.LPIC,
  T1.ENTERPRISE_SCALE,
  T1.LOANS_ACTUALLY,
  T1.ENTERPRISE_INVESTOR,
  T1.GUARANTY_STYLE,
  T1.LOAN_STATUS,
  T1.LOAN_QUALITY,
  T1.AREA_NO_ID
;